{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## PDF批量下载、解析、输出Excel文件"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. 环境准备"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: PyPDF2 in c:\\anaconda3\\lib\\site-packages (1.26.0)\n"
     ]
    }
   ],
   "source": [
    "# 在这里，即jupyter notebook中，可以直接安装\n",
    "!pip install PyPDF2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import requests\n",
    "import PyPDF2\n",
    "import random"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. 读取PDF链接文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(\"PDF下载地址.xlsx\")\n",
    "df.dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loan_no</th>\n",
       "      <th>PDF协议地址</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LN20210930230141334B7B43DC9ED46</td>\n",
       "      <td>https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LN20210930231229446436208C4FBA9</td>\n",
       "      <td>https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LN2021100111181824924236E86CD39</td>\n",
       "      <td>https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           loan_no  \\\n",
       "0  LN20210930230141334B7B43DC9ED46   \n",
       "1  LN20210930231229446436208C4FBA9   \n",
       "2  LN2021100111181824924236E86CD39   \n",
       "\n",
       "                                             PDF协议地址  \n",
       "0  https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...  \n",
       "1  https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...  \n",
       "2  https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建PDF下载目录\n",
    "dir_pdf = \"./download_pdfs/\"\n",
    "os.makedirs(dir_pdf, exist_ok=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. 批量下载文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "下载地址：https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.com/persistence/2021-10-01/保理合同（保理手续费一次性收取）.pdf?versionId=CAEQIBiBgIDG0pfq4RciIGFlZDYwZWQ1MGRmNDQ1MTFhZmQ3Yjg4MjViMjU5N2Vm\n",
      "下载地址：https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.com/persistence/2021-09-30/保理合同（保理手续费分期收取）.pdf?versionId=CAEQIBiBgIC5sqfd4RciIDRlOThiNjcyYjI2NTQ0MWM5MTBlYmJmMTgxYjJmMzQ2\n",
      "下载地址：https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.com/persistence/2021-10-01/保理合同（保理手续费分期收取）.pdf?versionId=CAEQIBiBgMDP3c3n4RciIGU3MzZlMzBhM2FmMTRjM2E4NjBlNzliYmExN2RkZGFl\n",
      "下载地址：https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.com/persistence/2021-10-01/保理合同（保理手续费分期收取）.pdf?versionId=CAEQIBiBgMCv.Jfq4RciIDg3N2I1NWVkZmU4MzQ3ZDI5YTFiNTMxNDIxMGQ1N2I5\n",
      "下载地址：https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.com/persistence/2021-10-01/保理合同（保理手续费分期收取）.pdf?versionId=CAEQIBiBgIDQ5Zfq4RciIGYxZWJiNWY1OWNiNTRlZTI4NTU3MGQxMzYzYTEzMzUx\n",
      "下载地址：https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.com/persistence/2021-10-01/保理合同（保理手续费分期收取）.pdf?versionId=CAEQIBiBgICypJfq4RciIDJjZjlmMDFjNDNiZTQ0MTc4ODZmNDRhMTFmZWIyN2Qz\n",
      "下载地址：https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.com/persistence/2021-10-01/保理合同（保理手续费一次性收取）.pdf?versionId=CAEQIBiBgICypM7q4RciIDgwZjA4ZjU4YzRhZjRkZjA5Yzk5NmViZjM0NWVkNGU3\n",
      "下载地址：https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.com/persistence/2021-10-01/保理合同（保理手续费一次性收取）.pdf?versionId=CAEQIBiBgMCxy87q4RciIDUzMzFhNmViZjVlNzQ2YzY5YjczNDYxOTExNGQ2MWM4\n",
      "下载地址：https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.com/persistence/2021-10-01/保理合同（保理手续费一次性收取）.pdf?versionId=CAEQIBiBgMCu3c7q4RciIGZiYjlmY2Y3MjEwODRlOWI5MDU0YmRhODE4NDRjOGRm\n",
      "下载地址：https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.com/persistence/2021-10-01/保理合同（保理手续费一次性收取）.pdf?versionId=CAEQIBiBgIDtmYXr4RciIGMyYzY3MGM1ZjE3ZjQxMWJiYzM5YTQ1ZjQxMTE4ZDQ1\n"
     ]
    }
   ],
   "source": [
    "for idx, row in df.iterrows():\n",
    "    loan_no = row[\"loan_no\"]\n",
    "    pdf_url = row[\"PDF协议地址\"]\n",
    "    \n",
    "    print(\"下载地址：\" + pdf_url)\n",
    "    \n",
    "    resp = requests.get(pdf_url)\n",
    "\n",
    "    with open(f\"{dir_pdf}/{loan_no}.pdf\", 'wb') as file:\n",
    "        file.write(resp.content)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. 批量解析PDF数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "from PyPDF2 import PdfFileReader"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LN20210930230141334B7B43DC9ED46 ./download_pdfs//LN20210930230141334B7B43DC9ED46.pdf\n",
      "LN20210930231229446436208C4FBA9 ./download_pdfs//LN20210930231229446436208C4FBA9.pdf\n",
      "LN2021100111181824924236E86CD39 ./download_pdfs//LN2021100111181824924236E86CD39.pdf\n",
      "LN202110011421043634D3F3FFBF5E5 ./download_pdfs//LN202110011421043634D3F3FFBF5E5.pdf\n",
      "LN20211001142107022D4479A0DD011 ./download_pdfs//LN20211001142107022D4479A0DD011.pdf\n",
      "LN20211001142109802B642FA228C94 ./download_pdfs//LN20211001142109802B642FA228C94.pdf\n",
      "LN20211001143051653192253DECDC7 ./download_pdfs//LN20211001143051653192253DECDC7.pdf\n",
      "LN2021100114511445199B392C279DC ./download_pdfs//LN2021100114511445199B392C279DC.pdf\n",
      "LN20211001145116979D9D03AE9B4F6 ./download_pdfs//LN20211001145116979D9D03AE9B4F6.pdf\n",
      "LN202110011519317308C18E1097B63 ./download_pdfs//LN202110011519317308C18E1097B63.pdf\n"
     ]
    }
   ],
   "source": [
    "# 提取保理合同信息\n",
    "results = [['贷款申请编号','保理合同编号','保理合同签署日期','基础合同/协议编号']] \n",
    "for pdf_file in os.listdir(dir_pdf):\n",
    "    fpdf_fpath = f\"{dir_pdf}/{pdf_file}\"\n",
    "    if not str(fpdf_fpath).endswith(\".pdf\"):\n",
    "        continue\n",
    "    \n",
    "    # xxx.pdf\n",
    "    loan_no = pdf_file.split(\".\")[0]\n",
    "    \n",
    "    print(loan_no, fpdf_fpath)\n",
    "    \n",
    "    # 读取PDF对象\n",
    "    pdf_reader = PdfFileReader(fpdf_fpath)\n",
    "    # 解析Fields\n",
    "    pdf_fields = pdf_reader.getFields()\n",
    "    \n",
    "    result_row = [loan_no]\n",
    "    for field in pdf_fields:\n",
    "        if field == 'contractNo':\n",
    "            contract_no = list(pdf_fields[field].values())[-1]\n",
    "            result_row.append(contract_no)\n",
    "        elif field == 'signDate':\n",
    "            sign_date = list(pdf_fields[field].values())[-1]\n",
    "            result_row.append(sign_date)\n",
    "        elif field == 'signContractNo':\n",
    "            sign_contract_no = list(pdf_fields[field].values())[-1]\n",
    "            result_row.append(sign_contract_no)\n",
    "\n",
    "    results.append(result_row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>贷款申请编号</th>\n",
       "      <th>保理合同编号</th>\n",
       "      <th>保理合同签署日期</th>\n",
       "      <th>基础合同/协议编号</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LN20210930230141334B7B43DC9ED46</td>\n",
       "      <td>2109302200000057-B</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>2109302200000057-F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LN20210930231229446436208C4FBA9</td>\n",
       "      <td>2109302000000008-B</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>2109302000000008-F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LN2021100111181824924236E86CD39</td>\n",
       "      <td>2110011100000243-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110011100000243-F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LN202110011421043634D3F3FFBF5E5</td>\n",
       "      <td>2110011100000871-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110011100000871-F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LN20211001142107022D4479A0DD011</td>\n",
       "      <td>2110011100000832-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110011100000832-F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>LN20211001142109802B642FA228C94</td>\n",
       "      <td>2110011000000939-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110011000000939-F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>LN20211001143051653192253DECDC7</td>\n",
       "      <td>2110011200000768-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110011200000768-F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>LN2021100114511445199B392C279DC</td>\n",
       "      <td>2110011200000687-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110011200000687-F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>LN20211001145116979D9D03AE9B4F6</td>\n",
       "      <td>2110011200000672-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110011200000672-F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>LN202110011519317308C18E1097B63</td>\n",
       "      <td>2110011500000269-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110011500000269-F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            贷款申请编号              保理合同编号    保理合同签署日期  \\\n",
       "0  LN20210930230141334B7B43DC9ED46  2109302200000057-B  2021-09-30   \n",
       "1  LN20210930231229446436208C4FBA9  2109302000000008-B  2021-09-30   \n",
       "2  LN2021100111181824924236E86CD39  2110011100000243-B  2021-10-01   \n",
       "3  LN202110011421043634D3F3FFBF5E5  2110011100000871-B  2021-10-01   \n",
       "4  LN20211001142107022D4479A0DD011  2110011100000832-B  2021-10-01   \n",
       "5  LN20211001142109802B642FA228C94  2110011000000939-B  2021-10-01   \n",
       "6  LN20211001143051653192253DECDC7  2110011200000768-B  2021-10-01   \n",
       "7  LN2021100114511445199B392C279DC  2110011200000687-B  2021-10-01   \n",
       "8  LN20211001145116979D9D03AE9B4F6  2110011200000672-B  2021-10-01   \n",
       "9  LN202110011519317308C18E1097B63  2110011500000269-B  2021-10-01   \n",
       "\n",
       "            基础合同/协议编号  \n",
       "0  2109302200000057-F  \n",
       "1  2109302000000008-F  \n",
       "2  2110011100000243-F  \n",
       "3  2110011100000871-F  \n",
       "4  2110011100000832-F  \n",
       "5  2110011000000939-F  \n",
       "6  2110011200000768-F  \n",
       "7  2110011200000687-F  \n",
       "8  2110011200000672-F  \n",
       "9  2110011500000269-F  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_result = pd.DataFrame(results[1:], columns=results[0])\n",
    "df_result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. 保存成结果Excel文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_result.to_excel(\"PDF提取结果-合同数据文件.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "6f8ee94c255eb1f45edb80e83721093c1db1e2ea85447c0854292673b957abb8"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
